package com.itheima.dao;

import com.itheima.pojo.Menu;
import com.itheima.pojo.Role;
import com.itheima.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Set;

public interface UserDao {

    @Select("select id from t_user where username=#{username}")
    public Integer getByUsername(String username);

    @Select("select distinct tm.* from t_user tu,t_role tr,t_menu tm,t_role_menu trm,t_user_role tur " +
            "where tu.id=tur.user_id and tr.id=tur.role_id and tr.id=trm.role_id " +
            "and trm.menu_id=tm.id and tu.id=#{userId} and tm.level=1")
    public List<Menu> getMenuLevelIsOne(Integer userId);
    @Select("select distinct\n" +
            "\ttm.* \n" +
            "from \n" +
            "\tt_user tu,t_role tr,t_menu tm,t_role_menu trm,t_user_role tur\n" +
            "where \n" +
            "\ttu.id=tur.user_id\n" +
            "and\n" +
            "\ttr.id=tur.role_id\n" +
            "and\n" +
            "\ttr.id=trm.role_id\n" +
            "and\n" +
            "\ttrm.menu_id=tm.id\n" +
            "and\n" +
            "\ttu.id=#{userId}\n" +
            "and\n" +
            "\ttm.level=2\n" +
            "and\n" +
            "\ttm.parentMenuId=#{menuId}")
    public List<Menu> getByParentMenuIdMenuLevelIsTwo(@Param("userId") Integer userId,@Param("menuId") Integer menuId);

    //////////////////////////////////////////////////////////上面是根据某个用户查询其所有能看到的菜单
    // 查询所有的用户

    //@Select("select id,birthday,gender,username,password,remark,station,telephone from t_user")
    @Select("select id,birthday,gender,username,password,remark,case when station=1 then '启用' else '禁用' end as station,telephone from t_user")
    //@Select("select * from t_user")
    public List<User> findPage();

    // 根据条件查询
    @Select("select id,birthday,gender,username,password,remark,case when station=1 then '启用' else '禁用' end as station,telephone from t_user where username like CONCAT('%',#{queryString},'%')")
    public List<User> findPageQueryString(String queryString);

    // 新增一个用户，并获取自增的ID
    @Insert("insert into t_user (username,password,gender,station,telephone,birthday,remark) " +
            " values(#{username},#{password},#{gender},#{station},#{telephone},#{birthday},#{remark})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    public void insert(User user);

    // 向角色-用户中间表中加入数据
    @Select("insert into t_user_role (user_id,role_id) values(#{userId},#{roleId}) ")
    public void insertUserIdAndRoleId(@Param("userId") Integer userId,@Param("roleId") Integer roleId);

    // 查询所有角色
    @Select("select * from t_role")
    public List<Role> getAllRole();

    // 查询某的用户
    @Select("select * from t_user where id=#{userId}")
    @Results({
            @Result(column = "id" , property = "id"),
            @Result(column = "birthday" , property = "birthday"),
            @Result(column = "gender" , property = "gender"),
            @Result(column = "username" , property = "username"),
            @Result(column = "password" , property = "password"),
            @Result(column = "remark" , property = "remark"),
            @Result(column = "station" , property = "station"),
            @Result(column = "telephone" , property = "telephone"),
            @Result(column = "id",property = "roles",javaType = java.util.Set.class,
            many = @Many(select = "com.itheima.dao.UserDao.getByRoles"))
    })
    public User findById(Integer userId);

    // 查询所有的角色，并将某个用户有的角色排在上面
    @Select("select id,name,keyword,case when id in (select role_id from t_user_role where user_id=#{userId}) then 1 else 0 end as flag from t_role ORDER BY flag DESC")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "keyword",property = "keyword")
    })
    public Set<Role> getByRoles(Integer userId);

    // 查询用户所有的角色ID
    @Select("select role_id from t_user_role where user_id = #{userId}")
    public List<Integer> getByRoleIds(Integer userId);

    // 修改用户信息
    @Update("update t_user set username=#{username},birthday=#{birthday},gender=#{gender},remark=#{remark},station=#{station},telephone=#{telephone} where id=#{id}")
    public void update(User user);
    // 删除用户角色中间表中某个用户的信息
    @Delete("delete from t_user_role where user_id=#{userId}")
    public void deleteRoleIdAndUserId(Integer userId);
    // 删除某个用户
    @Update("update t_user set station=2 where id=#{userId}")
    public void deleUser(Integer userId);

    // 查看 用户-角色 表中是否有 拥有某个角色的用户
    @Select("select count(*) from t_user_role where role_id=#{roleId}")
    public Integer getRoleIdCount(Integer roleId);


}
